package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;


import com.entity.Report_electri_Power;

/**   
* @Title: Report_electri_PowerDao.java 
* @Package com.interfaces 
* @author 孙梦楠 
* @date 2017年10月23日 上午9:47:24 
* @version V1.0.0   
*/

public interface Report_electri_PowerDao extends JpaRepository<Report_electri_Power, Integer>{
	
	@Transactional
	@Query(value="SELECT * FROM report_electri_power tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress=?1) AND (tri.code=?2) ORDER BY tri.time" ,nativeQuery=true)
	public List<Report_electri_Power> findTaskrecorditemByMeter(String meteraddress, String code,String time1,String time2);
	
	@Transactional
	@Query(value="SELECT * FROM report_electri_power tri WHERE (tri.time > ?3 OR  ?3 IS NULL) AND (tri.time < ?4 OR ?4 IS NULL) "
			+ "AND (tri.meteraddress IN (SELECT m.meter_address FROM e_meter m WHERE m.meter_org=?1 AND m.type=?5 AND m.meter_status != 4)) AND (tri.code=?2)" ,nativeQuery=true)
	public List<Report_electri_Power> findTaskrecorditemByMeter2(Integer meterorg, String code,String time1,String time2,String type);

	@Transactional
	@Query(value="SELECT COUNT( DISTINCT tri.meteraddress) FROM report_electri_power tri WHERE (tri.time = ?1 OR  ?1 IS NULL)  "
			+ "AND (tri.meteraddress IN (SELECT m.meter_address FROM e_meter m WHERE m.meter_org=?2 AND m.type=?3 AND m.meter_status != 4)) "
			+ "AND (tri.code=?4) " ,nativeQuery=true)
	public Integer customercountByMeter(String time,Integer meterorg, String type,String code);
	
	List<Report_electri_Power> findByMeteraddressOrderByTimeDesc(String meteraddress);
	
	List<Report_electri_Power> findByMeteraddressAndCodeOrderByTimeDesc(String meteraddress,String code);

	List<Report_electri_Power> findByTimeAndTerminaladdressAndCode(String time,String terminaladdress,String code);
	
	
	@Transactional
    @Modifying
    @Query("delete from Report_electri_Power rep where rep.terminaladdress=?1 AND rep.time=?2 AND rep.code = ?3")
    public void  deletedata(String terminaladdress,String time,String code);

}
